# -*- coding:utf-8 -*-
__author__ = 'liuyun'
__date__ = "2022-9-25"
__doc__ = "MySQL客户端"

# pymysql连接并且操作mysql数据库
#安装环境： pip3 install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple 

import pymysql
import traceback
from loguru import logger
from datetime import datetime
from config import FLASKCONFIG

class MySQLClinet:
    def __init__(self, DBCONFIG=FLASKCONFIG.MYSQLCONFIG):
        """
            初始化方法
        """
        self.conn = pymysql.connect(**DBCONFIG)
        self.cursor = self.conn.cursor()
        
    def close(self):
        """
            关闭数据库连接
        """
        self.cursor.close()
        self.conn.close()
    
    
    def query(self, sql):
        """
            查询方法
            sql： "select * from users where xxxx"
        """
        results = []
        try:
            self.cursor.execute(sql)
            
            desc_title = [x[0] for x in self.cursor.description]    # 获取row_title
            for item in self.cursor.fetchall():                     # 获取根据所有结果来构造{"k":"v"}
                item_dict = {}
                for i in range(len(item)):
                    if isinstance(item[i], datetime):       # 处理datetime格式的数据
                        item_dict[desc_title[i]] = item[i].strftime('%Y-%m-%d %H:%M:%S')
                    else:
                        item_dict[desc_title[i]] = item[i]
                results.append(item_dict)
        except:
            results = []
            logger.error(sql)
            traceback.print_exc()
        finally:
            self.close()
            
        return results      # 返回值是[{k:v, k1:v1}...]
    
    def commit(self, sql):
        """
            单条sql修改方法（insert update delete）
            sql : "update tb_user set xxx"
        """
        result = True
        try:
            res = self.cursor.execute(sql)
            self.conn.commit()
        except:
            result = False
            logger.error(sql)
            self.conn.rollback()
            traceback.print_exc()
        else:
            if res == 0:
                result = False
        finally:
            self.close()  
            
        return result
    
    def commit_many(self, sqls):
        """
            修改多条sql方法（insert update delete）
            sql： ["update tb_user set xxx", "update tb_user set xxx"]
        """
        result = True
        try:
            for sql in sqls:
                res = self.cursor.execute(sql)
                assert res == 1
            self.conn.commit()
        except:
            result = False
            logger.error(sql)
            self.conn.rollback()
            traceback.print_exc()
        finally:
            self.close()  
            
        return result
            
if __name__ == '__main__':
    # a = MySQLClinet().query("select * from tb_user where username='laozizuiniubi'")
    # print(a)
    sql = "UPDATE tb_case SET name='TGU上课用例2', apiIds='[4, 5, 6, 7, 8, 9]', moduleId='5', updateTime=now(), remark='TGU上课用例2' WHERE id='5';"
    a = MySQLClinet().commit(sql)
    print(a)